﻿Imports System.Data
Imports System.Data.OleDb
Imports geraLaudosAutomatico.conexao
Imports System.Text
Public Class municipioDAO

    
    Private i As Integer
    Private municipio As New municipioVO

    Public Function getMunicipios(ByVal id_destino As Integer) As Generic.List(Of municipioVO)

        Dim sql As New StringBuilder
        Dim objConn As New conexao

        sql.Clear()
        sql.Append("SELECT *FROM municipios INNER JOIN (destino INNER JOIN municipios_destino ON destino.id_destino = municipios_destino.id_destino) ON municipios.id_municipio = municipios_destino.id_municipio")
        sql.Append(" WHERE MUNICIPIOS_DESTINO.ID_DESTINO=@ID_DESTINO ORDER BY MUNICIPIOS.DESC_MUNICIPIO")


        Dim oReader As OleDbDataReader

        Dim lista As New Generic.List(Of municipioVO)



        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oComando.Parameters.AddWithValue("@ID_DESTINO", id_destino)

            oReader = oComando.ExecuteReader

            While oReader.Read
                municipio = New municipioVO
                municipio.ID_MUNICIPIO = IIf(IsDBNull(oReader("MUNICIPIOS.ID_MUNICIPIO")), Nothing, oReader("MUNICIPIOS.ID_MUNICIPIO"))
                municipio.DESC_MUNICIPIO = IIf(IsDBNull(oReader("DESC_MUNICIPIO")), Nothing, oReader("DESC_MUNICIPIO"))
                lista.Add(municipio)
            End While

            Return lista

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function



    Public Function getMunicipio(ByVal id_municipio As Integer) As municipioVO

        Dim sql As New StringBuilder
        Dim objConn As New conexao

        sql.Clear()
        sql.Append("SELECT * FROM departamento INNER JOIN (regional INNER JOIN municipios ON regional.id_regional = municipios.id_regional) ON departamento.id_departamento = regional.id_departamento WHERE ID_MUNICIPIO=@ID_MUNICIPIO ORDER BY DESC_MUNICIPIO")

        Dim oReader As OleDbDataReader

        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oComando.Parameters.AddWithValue("@ID_MUNICIPIO", id_municipio)

            oReader = oComando.ExecuteReader

            While oReader.Read
                municipio = New municipioVO
                municipio.ID_MUNICIPIO = IIf(Not IsDBNull("MUNICIPIOS.ID_MUNICIPIO"), Nothing, oReader("MUNICIPIOS.ID_MUNICIPIO"))
                municipio.DESC_MUNICIPIO = IIf(Not IsDBNull("DESC_MUNICIPIO"), Nothing, oReader("DESC_MUNICIPIO"))
                municipio.REGIONAL = IIf(Not IsDBNull("REGIONAL.ID_REGIONAL"), Nothing, New regionalVO With {.ID_REGIONAL = oReader("REGIONAL.ID_REGIONAL"), .NUMERO_REGIONAL = oReader("NUMERO_REGIONAL"), .CIDADE_REGIONAL = oReader("CIDADE_REGIONAL"), .DEPARTAMENTO = New departamentoVO With {.ID_DEPARTAMENTO = oReader("DEPARTAMENTO.ID_DEPARTAMENTO"), .NUMERO_DEPARTAMENTO = oReader("NUMERO_DEPARTAMENTO"), .CIDADE_DEPARTAMENTO = oReader("CIDADE_DEPARTAMENTO"), .STATUS = oReader("DEPARTAMENTO.STATUS")}, .STATUS = oReader("DEPARTAMENTO.STATUS")})
            End While

            Return municipio

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function

End Class
